package com.mengtian_tech.tju.dataquery.api.service;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.github.pagehelper.util.StringUtil;

/**
 * 查询车辆信息
 * @author wxd56
 */
@Service
public class QueryCarService {

	@Autowired
	@Qualifier("jdbcTemplateDataAi")
	JdbcTemplate jdbcTemplateDataAi;
	
	/**
	 * 查询车辆备案信息
	 * @param plateNo
	 */
	public List<Map<String,Object>> queryCardInfo(String plateNo,Integer pageNo,Integer pageSize) {
		
		
		//查询车牌号
		List<Map<String,Object>> list =  this.jdbcTemplateDataAi.queryForList("select * from car_enter_apply where "
				+ " (cph like ? or cphm like ? or cphm1 like ? or cphm2 like ? or cphm3 like ? or cphm4 like ? or cphm5 like ? ) and apply_status > 0  order by fkey_id desc  LIMIT ?,?",
				"%"+plateNo+"%","%"+plateNo+"%","%"+plateNo+"%","%"+plateNo+"%","%"+plateNo+"%","%"+plateNo+"%","%"+plateNo+"%",(pageNo-1)*pageSize,pageSize);
		
		return list;
	}
	
	/**
	 * 查询车辆备案信息
	 * @param plateNo
	 */
	public List<Map<String,Object>> queryCarByStaffId(String staffId) {
		
		
		//查询车牌号
		List<Map<String,Object>> list =  this.jdbcTemplateDataAi.queryForList("select * from car_enter_apply where "
				+ "  GZH = ?  and apply_status > 0  order by fkey_id desc  LIMIT 0,100",
				staffId);
		
		return list;
	}
	
	/**
	 * 查询进出记录
	 */
	public List<Map<String,Object>> queryParkLog(String plateNo,Integer pageNo) {
		if(pageNo == null) pageNo  = 1;
		int pageSize = 30;
		return this.jdbcTemplateDataAi.queryForList("select IN_REMARK,id,OUT_REMARK, IN_TIME,VOUCHER_NO,OUT_TIME,IN_PASS_NAME,user_type from car_park_record where VOUCHER_NO = ? order by id desc limit ?,?",
				plateNo,(pageNo-1)*pageSize,pageSize);
	}
	
	/**
	 * 查询进出记录
	 */
	public List<Map<String,Object>> queryParkLog(String school,String begin,String end,Integer pageNo) {
		if(pageNo == null) pageNo  = 1;
		int pageSize = 30;
		
		String sql = "select staff_id,user_type,staff_name, IN_REMARK,id,OUT_REMARK, IN_TIME,VOUCHER_NO,OUT_TIME,IN_PASS_NAME,college_name from car_park_record where 1=1 ";
		List<Object> params =  new ArrayList<Object>();
		
		if(StringUtil.isNotEmpty(school)) {
			sql += "and IN_PASS_NAME like ? ";
			params.add(school+"%");
		}
		
		if(StringUtil.isNotEmpty(begin)) {
			sql += "and date_on >= ? ";
			params.add(begin);
		}
		
		if(StringUtil.isNotEmpty(end)) {
			sql += "and date_on <= ? ";
			params.add(end);
		}
		
		sql += " order by id desc limit ?,?";
		params.add((pageNo-1)*pageSize);
		params.add(pageSize);
		
		return this.jdbcTemplateDataAi.queryForList(sql,params.toArray());
	}
	
	public List<Map<String,Object>> queryParkLog(String plateNo) {
		return this.queryParkLog(plateNo,1);
	}
	
	
	/**
	 * 查询车辆备案，根据姓名
	 */
	public List<Map<String,Object>> queryCarInfoByName(String name,Integer pageNo){
		if(pageNo == null) pageNo  = 1;
		int pageSize = 30;
		
		return this.jdbcTemplateDataAi.queryForList(
				"select * from car_enter_apply where ((xm like ?  and (    apply_type = '2'  OR apply_type = '3'  or  apply_type = '4'  ) ) or (DJFZR like ?  AND (apply_type='1')))  and apply_status > 0 order by fkey_id desc  LIMIT ?,? ",
				"%"+name+"%","%"+name+"%",(pageNo-1)*pageSize,pageSize);
	}
	
	
	/**
	 * 获取车辆备案
	 */
	public Date getCarAsyncDate() {
		Map<String,Object>  map = this.jdbcTemplateDataAi.queryForMap("select task_begin from async_log_car_apply order by id desc limit 0,1");
		return (Date) map.get("task_begin");
	}
	
	public Date getCarParkAsyncDate() {
		Map<String,Object>  map = this.jdbcTemplateDataAi.queryForMap("select task_begin from async_log_car_gate order by id desc limit 0,1");
		return (Date) map.get("task_begin");
	}

	public Map<String, Object> selectCarById(Long id) {
		return this.jdbcTemplateDataAi.queryForMap("select * from car_enter_apply where id = ? ",id);
	}
	

	
	/**
	 * 查询车辆备案信息
	 * @param plateNo
	 */
	public List<Map<String,Object>> getCardInfoList(Integer pageNo) {
		
		int pageSize = 20;
		
		//查询车牌号
		List<Map<String,Object>> list =  this.jdbcTemplateDataAi.queryForList("select * from car_enter_apply where "
				+ "  apply_status > 0  order by fkey_id desc  LIMIT ?,?",
				(pageNo-1)*pageSize,pageSize);
		
		return list;
	}
	

	/**
	 * 查询车辆备案信息
	 */
	public List<Map<String,Object>> getCarInfoList(Integer pageNo,int type, int enterDate, int personCount,Date beginDate , Date endDate ) {
		
		int pageSize = 20;
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from car_enter_apply where   apply_status > 0  ";
		
		if(type != 0) {
			sql += " and apply_type = ? ";
			params.add(type);
		}
		
		if(enterDate > 0) {
			sql += " and  txrq  >=  ?  and txrq <= ? ";
			
			Calendar c = Calendar.getInstance();
			c.set(Calendar.HOUR_OF_DAY, 0);
			c.set(Calendar.MINUTE, 0);
			c.set(Calendar.SECOND,0);
			c.set(Calendar.MILLISECOND, 0);
			
			Date now = c.getTime();
			params.add(now);
			
			if(enterDate == 1) {
				params.add(now);
			}else if(enterDate == 2) {
				c.add(Calendar.DATE, 3);
				params.add(c.getTime());
			}
			else if(enterDate == 3) {
				c.add(Calendar.DATE, 30);
				params.add(c.getTime());
			}
		}
		
		if(beginDate != null && endDate != null) {
			
			sql += " and  txrq  >=  ?  and txrq <= ? ";			
			params.add(beginDate);
			params.add(endDate);
		}
		
		if(personCount != 0) {
			sql += " and CSRYSL >= ? and CSRYSL <= ? ";  
			if(personCount == 1) {
				params.add(1);
				params.add(10);
			}
			if(personCount == 2) {
				params.add(10);
				params.add(20);
			}
			if(personCount == 3) {
				params.add(20);
				params.add(30);
			}
			if(personCount == 4) {
				params.add(30);
				params.add(40);
			}
			if(personCount == 5) {
				params.add(40);
				params.add(10000);
			}
		}
		
		
		sql += " order by fkey_id desc  LIMIT ?,? ";
		params.add((pageNo-1)*pageSize);
		params.add(pageSize);
		
		System.out.println(sql);
		System.out.println(params);
		
		
		//查询车牌号
		List<Map<String,Object>> list =  this.jdbcTemplateDataAi.queryForList(sql,params.toArray());
		
		return list;
	}
}
